53  数据框拼接

本章定位(复习与补充训练层):本章对应正课第12章《Pandas 数据框拼接》(章节 12),用于复习、补缺与额外练习。建议先不看讲解,直接尝试下方平台任务,再对照解析补弱项。本章不属于必修主线。先做本章『动手与思考』第 1 题与平台任务自测,通过即可跳过本章。

53.1 本章学习目标

通过本章的复习与补充训练,你将能够:

  1. 说出 concat、merge、join 各自的适用场景,并按轴向(纵向/横向)与键匹配方式选用
  2. 画出 inner/left/right/outer 四种连接的结果集示意,并预测连接后表的行数变化
  3. 对金融时间序列完成按日期索引的对齐拼接,并处理对不齐产生的缺失
  4. 独立完成本章平台任务,再对照讲解补弱项

为什么”把数据拼到一起”会单独成为一章?先看金融分析中多源整合的真实难点。

53.2 引言多源数据整合的挑战

在真实的数据分析项目中,数据往往分散在多个来源中。对于金融分析师而言,将不同来源的数据整合在一起是日常工作的核心挑战。

53.2.1 金融数据整合的典型场景

多源数据整合的必要性:

  • 行情数据:来自交易所的实时价格、成交量数据
  • 财务数据:来自公司财报的资产负债表、利润表数据
  • 宏观数据:来自统计部门的GDP、CPI、利率数据
  • 情绪数据:来自新闻舆情、社交媒体的投资者情绪指标

53.2.2 数据整合的核心问题

为什么数据整合如此复杂?

  1. 粒度不匹配:日频行情数据 vs 季频财务数据
  2. 时间对齐:不同市场的交易日历不同(如A股 vs 美股)
  3. 键值识别:如何正确匹配同一公司的不同数据源?
  4. 重复数据:同一指标可能来自多个提供商,如何去重?
  5. 性能瓶颈:大规模数据集的合并操作可能极其耗时

53.3 数据拼接的数学基础

53.3.1 垂直拼接(行向堆叠)

定义:将多个数据集沿行方向(纵向)堆叠,增加观测数量。

设两个数据矩阵:

\[ D_1 = \begin{bmatrix} x_{11} & x_{12} \\ x_{21} & x_{22} \\ \vdots & \vdots \\ x_{n1} & x_{n2} \end{bmatrix}, \quad D_2 = \begin{bmatrix} y_{11} & y_{12} \\ y_{21} & y_{22} \\ \vdots & \vdots \\ y_{m1} & y_{m2} \end{bmatrix} \]

垂直拼接结果:

\[ D_{\text{concat}} = \begin{bmatrix} D_1 \\ D_2 \end{bmatrix} = \begin{bmatrix} x_{11} & x_{12} \\ \vdots & \vdots \\ x_{n1} & x_{n2} \\ y_{11} & y_{12} \\ \vdots & \vdots \\ y_{m1} & y_{m2} \end{bmatrix} \]

前提条件:两个数据集必须具有相同的列结构(相同列名和数据类型)。

53.3.2 水平拼接(列向合并)

定义:将多个数据集沿列方向(横向)合并,增加变量数量。

\[ D_{\text{merge}} = [D_1 \mid D_2] = \begin{bmatrix} x_{11} & x_{12} & y_{11} & y_{12} \\ \vdots & \vdots & \vdots & \vdots \\ x_{n1} & x_{n2} & y_{n1} & y_{n2} \end{bmatrix} \]

前提条件:两个数据集必须具有相同的行数或可通过键值对齐。

53.3.3 关系代数基础

Pandas的merge操作基于关系代数(Relational Algebra)中的连接(Join)运算:

\[ R \bowtie_{\theta} S = \{ (r, s) \in R \times S \mid \theta(r, s) \} \]

其中:

  • \(R, S\): 两个关系(数据表)
  • \(\bowtie\): 连接运算符
  • \(\theta\): 连接条件(通常是键值相等)

53.4 concat函数垂直拼接的首选工具

53.4.1 基础语法与参数

平台任务1(平台原始代码)

以下代码与教学平台任务要求完全一致:

任务要求:从同一工作簿分别读入 Sheet1 与 Sheet2 两份股票收盘价数据,并各查看前五行与后五行。请将代码原样输入教学平台(注释除外),判定以平台为准。

列表 53.1: 平台原始代码 (1) 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import pandas as pd  # 导入Pandas数据分析库

price_JantoMar = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet1",header=0,index_col=0)#从外部导入Sheet1的5只股票信息链接为https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx

print(price_JantoMar.head()) #查看前五行数据

print(price_JantoMar.tail()) #查看后五行数据

price_AprtoJui = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet2",header=0,index_col=0)##从外部导入Sheet2的5只股票信息链接为https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx

print(price_AprtoJui.head()) #查看前五行数据

print(price_AprtoJui.tail()) #查看后五行数据

预期输出(OSS 直链本机实测,Sheet1 为中国移动、中国电信、中国人寿 146 个交易日(2019-01-02 至 07-31)的收盘价,Sheet2 为中国铝业、中国海洋石油同期 146 个交易日的收盘价;具体以平台运行结果为准):

             中国移动   中国电信   中国人寿
日期
2019-01-02  47.51  50.60  10.44
2019-01-03  47.39  49.53  10.09
2019-01-04  49.23  50.44  10.55
2019-01-07  49.91  51.08  10.59
2019-01-08  50.33  51.36  10.72
             中国移动   中国电信   中国人寿
日期
2019-07-25  43.41  46.17  13.08
2019-07-26  43.48  45.58  13.08
2019-07-29  43.29  45.39  13.00
2019-07-30  42.85  45.00  12.86
2019-07-31  42.60  44.74  12.74
            中国铝业  中国海洋石油
日期
2019-01-02  7.88  150.00
2019-01-03  7.63  147.59
2019-01-04  7.98  155.74
2019-01-07  8.15  157.99
2019-01-08  8.48  161.07
            中国铝业  中国海洋石油
日期
2019-07-25  8.21  167.80
2019-07-26  8.29  166.82
2019-07-29  8.30  167.54
2019-07-30  8.21  167.00
2019-07-31  8.05  165.33

平台任务2(平台原始代码)

以下代码与教学平台任务要求完全一致:

任务要求:重新读入两份数据,用 concat 的 axis=0 把它们沿行方向纵向拼接,并查看拼接结果的前五行与后五行。请将代码原样输入教学平台(注释除外),判定以平台为准。

列表 53.2: 平台原始代码 (2) 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import pandas as pd  # 导入Pandas数据分析库

# 从Excel文件读取数据存入price_JantoMar
price_JantoMar = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet1",header=0,index_col=0)

# 从Excel文件读取数据存入price_AprtoJui
price_AprtoJui = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet2",header=0,index_col=0)

price_JantoJul = pd.concat([price_JantoMar,price_AprtoJui],axis=0) #使用concat函数按行拼接

print(price_JantoJul.head())  #前五行数据

print(price_JantoJul.tail())  #后五行数据

预期输出(OSS 直链本机实测;具体以平台运行结果为准):

两表的列名不同,纵向拼接取列并集,得到 292 行 × 5 列;前 146 行来自 Sheet1,中国铝业两列为 NaN;后 146 行来自 Sheet2,前三列为 NaN。head 与 tail 如下:

             中国移动   中国电信   中国人寿  中国铝业  中国海洋石油
日期
2019-01-02  47.51  50.60  10.44   NaN     NaN
2019-01-03  47.39  49.53  10.09   NaN     NaN
2019-01-04  49.23  50.44  10.55   NaN     NaN
2019-01-07  49.91  51.08  10.59   NaN     NaN
2019-01-08  50.33  51.36  10.72   NaN     NaN
             中国移动  中国电信  中国人寿  中国铝业  中国海洋石油
日期
2019-07-25   NaN   NaN   NaN  8.21  167.80
2019-07-26   NaN   NaN   NaN  8.29  166.82
2019-07-29   NaN   NaN   NaN  8.30  167.54
2019-07-30   NaN   NaN   NaN  8.21  167.00
2019-07-31   NaN   NaN   NaN  8.05  165.33

判读要点:两份 Sheet 的日期范围完全相同(2019-01-02 至 07-31),纵向拼接后行标签成对重复——这正是本章小结提醒的”默认保留原索引,用 loc 取行会一次取出多行”的实例。

平台任务3(平台原始代码)

以下代码与教学平台任务要求完全一致:

任务要求:再次读入两份 Sheet 数据并各查看首尾五行,为任务四的按列拼接准备数据。请将代码原样输入教学平台(注释除外),判定以平台为准。

列表 53.3: 平台原始代码 (3) 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import pandas as pd  # 导入Pandas数据分析库

price_3stocks = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet1",header=0,index_col=0) #导入数据Sheet1 链接https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx

print(price_3stocks.head()) #查看前五行数据

print(price_3stocks.tail()) #查看后五行数据

price_2stocks = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet2",header=0,index_col=0) #导入数据Sheet1 链接https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx

print(price_2stocks.head()) #查看前五行数据

print(price_2stocks.tail()) #查看后五行数据

预期输出(OSS 直链本机实测;具体以平台运行结果为准):

与任务一完全相同——读入的表与打印语句一致,四段输出依次为 Sheet1 的 head、tail 与 Sheet2 的 head、tail(数值见任务一的预期输出)。

平台任务4(平台原始代码)

以下代码与教学平台任务要求完全一致:

任务要求:分别用 concat(axis=1)、merge(按行索引匹配)与 join 三种方式把两份数据沿列方向横向拼接,并各查看前五行。请将代码原样输入教学平台(注释除外),判定以平台为准。

列表 53.4: 平台原始代码 (4) 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import pandas as pd  # 导入Pandas数据分析库

# 从Excel文件读取数据存入price_3stocks
price_3stocks = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet1",header=0,index_col=0)

# 从Excel文件读取数据存入price_2stocks
price_2stocks = pd.read_excel("https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx",sheet_name="Sheet2",header=0,index_col=0)

price_5stocks_concat = pd.concat([price_3stocks,price_2stocks],axis=1) #使用concat函数按列拼接

print(price_5stocks_concat.head()) #查看前五行数据

price_5stocks_merge = pd.merge(left=price_3stocks,right=price_2stocks,left_index=True,right_index=True) #使用merge函数按列拼接

print(price_5stocks_merge.head()) #查看前五行数据

price_5stocks_join = price_3stocks.join(price_2stocks,on="日期") #用join函数按列拼接

print(price_5stocks_join.head()) #查看前五行数据

预期输出(OSS 直链本机实测;具体以平台运行结果为准):

两表的行索引(日期)完全一致,三种拼接方式在本数据上结果相同,均为 146 行 × 5 列、无缺失;print 连打三遍相同的前五行:

             中国移动   中国电信   中国人寿  中国铝业  中国海洋石油
日期
2019-01-02  47.51  50.60  10.44  7.88  150.00
2019-01-03  47.39  49.53  10.09  7.63  147.59
2019-01-04  49.23  50.44  10.55  7.98  155.74
2019-01-07  49.91  51.08  10.59  8.15  157.99
2019-01-08  50.33  51.36  10.72  8.48  161.07

判读要点:join(on="日期") 在这里能对齐成功,是因为调用方存在名为”日期”的行索引(其作用相当于按索引对齐的键);若调用方既没有名为”日期”的列、行索引也不叫”日期”,这句会直接报 KeyError: '日期'。

列表 53.5: 使用concat进行垂直拼接 
# =============================================================================
# 题目：使用concat进行垂直拼接
# =============================================================================
# 本任务演示如何使用pd.concat()函数将多个数据框垂直拼接（沿行方向堆叠）
# 场景：将多只股票的收益率数据合并成一个长格式数据框

# ==================== 导入必要的库 ====================
import pandas as pd  # Pandas数据分析库
import numpy as np  # NumPy数值计算库

# ==================== 创建股票A的收益率数据 ====================
# 场景：贵州茅台(600519.SH)连续3个交易日的收益率数据
# pd.date_range()：生成日期范围，'2024-01-01'为起始日期，periods=3表示生成3个日期
stock_a_returns = pd.DataFrame({
    '日期': pd.date_range('2024-01-01', periods=3),  # 生成3个连续日期
    '股票代码': ['600519.SH'] * 3,  # 股票代码重复3次（列表乘法）
    '收益率': [0.02, -0.01, 0.03]  # 3个交易日的收益率：2%, -1%, 3%
})

# ==================== 创建股票B的收益率数据 ====================
# 场景：五粮液(000858.SZ)接下来的3个交易日收益率数据
# 注意：这里的起始日期是'2024-01-04'，正好接续股票A的最后日期
stock_b_returns = pd.DataFrame({
    '日期': pd.date_range('2024-01-04', periods=3),  # 从2024-01-04开始生成3个日期
    '股票代码': ['000858.SZ'] * 3,  # 五粮液的股票代码
    '收益率': [0.01, 0.02, -0.02]  # 3个交易日的收益率：1%, 2%, -2%
})

# ==================== 使用concat进行垂直拼接 ====================
# pd.concat()：拼接函数，将多个数据框沿指定轴拼接
# 参数说明：
#   [stock_a_returns, stock_b_returns]：要拼接的数据框列表
#   ignore_index=True：忽略原始索引，重新生成从0开始的连续索引
#     - False（默认）：保留原始索引，可能出现重复索引
#     - True：重置索引为0, 1, 2, ..., n-1
all_returns = pd.concat([stock_a_returns, stock_b_returns], ignore_index=True)

# ==================== 打印原始数据 ====================
print('股票A数据:')
print(stock_a_returns)
# 输出解读：贵州茅台3天的收益率数据，索引为0, 1, 2

print('\n股票B数据:')
print(stock_b_returns)
# 输出解读：五粮液3天的收益率数据，索引为0, 1, 2

# ==================== 打印拼接结果 ====================
print('\n拼接结果:')
print(all_returns)
# 输出解读：两个数据框垂直拼接，共6行数据
# ignore_index=True确保索引从0到5连续递增
# 如果ignore_index=False，索引会是0,1,2,0,1,2（重复）

关键参数解析:

参数 作用 默认值 推荐用法
objs 要拼接的对象列表 必需 使用列表 [df1, df2, ...]
axis 拼接方向(0=行,1=列) 0 axis=0垂直,axis=1水平
ignore_index 忽略原索引,重新生成 False 拼接后通常设为True
keys 创建多级索引标识来源 None 需要追溯数据源时使用
join 列对齐方式(‘inner’/‘outer’) ‘outer’ ’inner’只保留共有列
sort 是否对列排序 True 大数据集设为False提升性能

53.4.2 多级索引的应用

场景:需要追踪每条数据的来源。

列表 53.6: 使用concat创建多级索引 
# =============================================================================
# 题目：使用concat创建多级索引
# =============================================================================
# 本任务演示如何使用keys参数在拼接时创建多级索引，以便追溯数据来源
# 场景：多源数据质量检查时，需要快速定位问题数据来自哪个数据源

# ==================== 使用keys参数创建多级索引 ====================
# keys参数：为每个输入的数据框分配一个键值，用于创建多级索引
# 参数说明：
#   [stock_a_returns, stock_b_returns]：要拼接的数据框列表
#   keys=['股票A', '股票B']：为两个数据框分别指定标识键
#   names=['数据源', '行号']：给多级索引的每一级命名
multi_index_returns = pd.concat(
    [stock_a_returns, stock_b_returns],
    keys=['股票A', '股票B'],  # 第一级索引：标识数据来源
    names=['数据源', '行号']  # 给两级索引分别命名
)

# ==================== 打印多级索引结构 ====================
print('多级索引结构:')
print(multi_index_returns)
# 输出解读：
# - 索引变成了两级行：(股票A, 0), (股票A, 1), (股票A, 2), (股票B, 0), (股票B, 1), (股票B, 2)
# - 第一级'数据源'标识数据来自股票A还是股票B
# - 第二级'行号'保留原始数据框的行索引
# - 这种结构便于后续按数据源筛选或分析

print('\n索引信息:')
print(multi_index_returns.index)
# 输出解读：显示多级索引的完整结构，包括索引名称和级别

# ==================== 选择特定来源的数据 ====================
# multi_index_returns.loc['股票A']：使用第一级索引'数据源'进行筛选
# .loc[]：按标签索引，这里只指定第一级索引'股票A'，会选出所有第二级索引的数据
print('\n仅选择股票A的数据:')
print(multi_index_returns.loc['股票A'])
# 输出解读：只显示来自股票A的3行数据
# 应用场景：发现某天数据异常时，可以快速定位是哪个数据源的问题

金融应用:多源数据质量检查时,可通过多级索引快速定位问题数据的来源。

53.5 merge函数基于键值的水平合并

53.5.1 基础连接操作

列表 53.7: 使用merge进行基础连接 
# =============================================================================
# 题目：使用merge进行基础连接
# =============================================================================
# 本任务演示如何使用pd.merge()函数基于共同的键值（股票代码）进行水平合并
# 场景：将股票基本信息与财务数据合并，得到完整的分析数据集

# ==================== 创建股票基本信息数据 ====================
# 场景：3只A股的基本信息（股票代码、名称、行业）
stock_info = pd.DataFrame({
    '股票代码': ['600519.SH', '000858.SZ', '600036.SH'],  # 3只股票的代码
    '股票名称': ['贵州茅台', '五粮液', '招商银行'],  # 对应的股票名称
    '行业': ['食品饮料', '食品饮料', '金融']  # 所属行业
})

# ==================== 创建股票财务数据 ====================
# 场景：3只股票的估值指标（注意：股票代码与基本信息不完全相同）
financial_data = pd.DataFrame({
    '股票代码': ['600519.SH', '000858.SZ', '601318.SH'],  # 注意：第3只是中国平安，不是招商银行
    'PE': [35.2, 25.8, 10.5],  # 市盈率（Price-to-Earnings ratio）
    'PB': [12.5, 8.3, 1.2]  # 市净率（Price-to-Book ratio）
})

# ==================== 内连接（Inner Join）====================
# pd.merge()：基于键值合并两个数据框
# 参数说明：
#   stock_info, financial_data：要合并的两个数据框
#   on='股票代码'：指定合并的键值列（两边都有这一列）
#   how='inner'：内连接，只保留键值在两边都存在的行
#     - inner（默认）：交集，只保留两边都有的键
#     - left：保留左表所有行
#     - right：保留右表所有行
#     - outer：并集，保留所有键
inner_result = pd.merge(
    stock_info,
    financial_data,
    on='股票代码',  # 基于股票代码列进行匹配
    how='inner'  # 内连接，只保留两边都有的股票
)

# ==================== 打印原始数据 ====================
print('股票基本信息:')
print(stock_info)
# 输出解读：包含3只股票（贵州茅台、五粮液、招商银行）

print('\n财务数据:')
print(financial_data)
# 输出解读：包含3只股票（贵州茅台、五粮液、中国平安）
# 注意：招商银行(600036.SH)在基本信息中，但不在财务数据中
#       中国平安(601318.SH)在财务数据中，但不在基本信息中

# ==================== 打印内连接结果 ====================
print('\n内连接结果(只保留两边都有的股票):')
print(inner_result)
# 输出解读：只有2只股票（贵州茅台、五粮液）被保留
# 原因：招商银行和中国平安的代码只在一边出现，被内连接过滤掉了
# 应用场景：确保分析的股票同时具备基本信息和财务数据，避免缺失值

内连接的数学含义:

\[ R \bowtie S = \{ (r, s) \mid r[\text{key}] = s[\text{key}] \} \]

只有键值在两个数据集中都存在的行才会被保留。

53.5.2 连接类型的完整对比

列表 53.8: 四种连接类型的对比 
# =============================================================================
# 题目：四种连接类型的对比
# =============================================================================
# 本任务演示merge函数的四种连接类型（inner/left/right/outer）的区别
# 场景：根据不同的业务需求，选择合适的连接方式整合数据

# ==================== 左连接（Left Join）====================
# how='left'：保留左表（stock_info）的所有行
# 右表（financial_data）中匹配不到的行，其列填充为NaN（缺失值）
left_result = pd.merge(stock_info, financial_data, on='股票代码', how='left')
# 输出预期：
# - 贵州茅台：匹配成功，完整数据
# - 五粮液：匹配成功，完整数据
# - 招商银行：左表有但右表无，PE和PB列填充为NaN

# ==================== 右连接（Right Join）====================
# how='right'：保留右表（financial_data）的所有行
# 左表（stock_info）中匹配不到的行，其列填充为NaN
right_result = pd.merge(stock_info, financial_data, on='股票代码', how='right')
# 输出预期：
# - 贵州茅台：匹配成功，完整数据
# - 五粮液：匹配成功，完整数据
# - 中国平安：右表有但左表无，股票名称和行业列填充为NaN

# ==================== 外连接（Outer Join）====================
# how='outer'：保留所有行（左右表的并集）
# 匹配不到的列都填充为NaN
outer_result = pd.merge(stock_info, financial_data, on='股票代码', how='outer')
# 输出预期：
# - 贵州茅台：匹配成功，完整数据
# - 五粮液：匹配成功，完整数据
# - 招商银行：只在左表，右表的PE、PB列为NaN
# - 中国平安：只在右表，左表的股票名称、行业列为NaN

# ==================== 打印各种连接结果 ====================
print('左连接结果(保留左边所有股票):')
print(left_result)
# 输出解读：招商银行被保留，但其财务指标为NaN
# 应用场景：基本信息是主表，不能丢失任何股票，财务数据只是补充信息

print('\n右连接结果(保留右边所有股票):')
print(right_result)
# 输出解读：中国平安被保留，但其基本信息为NaN
# 应用场景：财务数据是主表，需要确保所有有财务数据的股票都被分析

print('\n外连接结果(保留所有股票,缺失值填充为NaN):')
print(outer_result)
# 输出解读：所有4只股票都被保留，缺失的相应位置填充为NaN
# 应用场景：最大化信息利用，后续可以分析哪些股票缺失哪些数据

连接类型的决策树:

是否需要保留左边所有数据?
├─ 是 → 使用 left join
└─ 否 → 是否需要保留右边所有数据?
    ├─ 是 → 使用 right join
    └─ 否 → 是否需要保留所有数据?
        ├─ 是 → 使用 outer join
        └─ 否 → 使用 inner join (最严格)

金融应用指南:

场景 推荐连接类型 理由
主数据表匹配补充信息 left 保证主表数据不丢失
数据源可靠性相同 inner 只保留两边都有的高质量数据
整合多个不完整来源 outer 最大化信息利用,后续处理缺失值

53.6 join方法索引对齐的便捷工具

列表 53.9: 使用join基于索引合并 
# =============================================================================
# 题目：使用join基于索引合并
# =============================================================================
# 本任务演示如何使用df.join()方法基于索引进行数据合并
# join是merge的特例，专门用于基于索引合并，代码更简洁
# 场景：两个数据框都已将股票代码设为索引，需要基于索引合并

# ==================== 创建以股票代码为索引的数据 ====================
# 场景：收益率数据，以股票代码为行索引
returns = pd.DataFrame({
    '日收益率': [0.02, 0.01, -0.01]  # 3只股票的日收益率
}, index=['600519.SH', '000858.SZ', '600036.SH'])  # 将股票代码设为索引

# 场景：波动率数据，也以股票代码为行索引
# 注意：第3只股票是中国平安(601318.SH)，与收益率数据不同
volatility = pd.DataFrame({
    '年化波动率': [0.25, 0.30, 0.20]  # 3只股票的年化波动率
}, index=['600519.SH', '000858.SZ', '601318.SH'])  # 股票代码索引

# ==================== 基于索引进行左连接 ====================
# df.join()：基于索引合并两个数据框
# 参数说明：
#   volatility：要合并的右表
#   how='left'：左连接，保留左表（returns）的所有索引
#              右表中匹配不到的索引，其列填充为NaN
joined_data = returns.join(volatility, how='left')
# 等价于：pd.merge(returns, volatility, left_index=True, right_index=True, how='left')
# 但join的代码更简洁，专门针对基于索引的合并场景

# ==================== 打印原始数据 ====================
print('收益率数据(以股票代码为索引):')
print(returns)
# 输出解读：3只股票的收益率，索引是股票代码

print('\n波动率数据(以股票代码为索引):')
print(volatility)
# 输出解读：3只股票的波动率，索引也是股票代码
# 注意：招商银行(600036.SH)在收益率中，但不在波动率中
#       中国平安(601318.SH)在波动率中，但不在收益率中

# ==================== 打印基于索引的左连接结果 ====================
print('\n基于索引的左连接结果:')
print(joined_data)
# 输出解读：
# - 贵州茅台(600519.SH)：两边都有，完整数据
# - 五粮液(000858.SZ)：两边都有，完整数据
# - 招商银行(600036.SH)：只在左表，年化波动率列为NaN
# - 中国平安(601318.SH)：不在结果中，因为左连接不保留右表独有的索引

join vs merge的选用原则:

  • 使用join: 数据已经以键值为索引,代码更简洁
  • 使用merge: 需要基于列进行连接,或需要更复杂的连接条件

53.7 金融应用多源数据整合案例

53.7.1 场景上市公司多维数据整合

任务:整合股票基本信息、行情数据、财务指标,构建完整的分析数据集。

列表 53.10: 金融多源数据整合实战 
# =============================================================================
# 题目：金融多源数据整合实战
# =============================================================================
# 本任务演示如何逐步整合多个数据源，构建完整的股票分析数据集
# 场景：整合股票基本信息、日行情数据、季度财务指标

# ==================== 导入必要的库 ====================
import pandas as pd

# ==================== 数据源1：股票基本信息 ====================
# 场景：4只A股的基本信息（代码、名称、上市日期、行业）
# 这是主表，后续合并时以这个表为基础（左连接）
stock_basic = pd.DataFrame({
    '股票代码': ['600519.SH', '000858.SZ', '600036.SH', '601318.SH'],
    '股票名称': ['贵州茅台', '五粮液', '招商银行', '中国平安'],
    '上市日期': ['2001-08-27', '1998-04-27', '2002-04-09', '2007-03-01'],
    '行业': ['食品饮料', '食品饮料', '金融', '金融']
})

# ==================== 数据源2：日行情数据（某日）====================
# 场景：某日的收盘价和涨跌幅数据
# 注意：只有3只股票有行情数据，中国平安缺失
daily_quote = pd.DataFrame({
    '股票代码': ['600519.SH', '000858.SZ', '600036.SH'],
    '收盘价': [1850.00, 158.50, 32.80],  # 当日收盘价（元）
    '涨跌幅': [1.5, -0.8, 0.5]  # 当日涨跌幅（%）
})

# ==================== 数据源3：财务指标（季频）====================
# 场景：最新季度的财务指标（ROE、负债率）
# 注意：只有3只股票有财务数据，招商银行缺失
financial_metrics = pd.DataFrame({
    '股票代码': ['600519.SH', '000858.SZ', '601318.SH'],
    'ROE': [25.8, 22.3, 15.6],  # 净资产收益率（Return on Equity，%）
    '负债率': [18.5, 30.2, 92.5]  # 资产负债率（%）
})

# ==================== 步骤1：以基本信息为主表，左连接行情数据 ====================
# pd.merge()：基于股票代码合并基本信息和行情数据
# 参数说明：
#   stock_basic：左表（主表），包含所有股票的基本信息
#   daily_quote：右表，包含当日的行情数据
#   on='股票代码'：基于股票代码列进行匹配
#   how='left'：左连接，保留左表（基本信息）的所有股票
#              右表中匹配不到的股票，其行情数据列填充为NaN
#   indicator=True：添加一列'_merge'，标识每行数据的来源
#                  - 'both'：两边都有
#                  - 'left_only'：只在左表
#                  - 'right_only'：只在右表
step1 = pd.merge(
    stock_basic,
    daily_quote,
    on='股票代码',
    how='left',  # 左连接，确保所有股票都被保留
    indicator=True  # 添加_merge列标识数据来源
)

print('步骤1:基本信息 + 行情数据(左连接)')
print(step1)
# 输出解读：中国平安的收盘价和涨跌幅为NaN（因为行情数据中没有这只股票）

# ==================== 步骤2：继续左连接财务指标 ====================
# pd.merge()：将步骤1的结果与财务数据继续合并
# 参数说明：
#   step1：左表，已经包含基本信息+行情数据
#   financial_metrics：右表，包含财务指标
#   on='股票代码'：继续基于股票代码匹配
#   how='left'：左连接，保留左表的所有股票
#   suffixes=('', '_财务')：处理列名冲突
#                        - 如果两个数据框有重名列，分别添加后缀区分
#                        - 这里只是示例，实际没有重名列
final_data = pd.merge(
    step1,
    financial_metrics,
    on='股票代码',
    how='left',
    suffixes=('', '_财务')  # 处理潜在的列名冲突
)

print('\n最终整合结果:')
print(final_data)
# 输出解读：
# - 贵州茅台、五粮液：完整数据（基本信息、行情、财务都有）
# - 招商银行：缺失财务指标（ROE和负债率为NaN）
# - 中国平安：缺失行情数据（收盘价和涨跌幅为NaN）

# ==================== 分析数据完整性 ====================
print('\n数据完整性分析:')
# 选择关键列并重命名，方便阅读
# []：选择列，.rename()：重命名列
print(final_data[['股票代码', '股票名称', '_merge']].rename(columns={'_merge': '行情数据'}))
# 输出解读：_merge列显示哪些股票有行情数据（'both'），哪些没有（'left_only'）

print('\n缺失值统计:')
# .isna().sum()：统计每列的缺失值数量
print(final_data.isna().sum())
# 输出解读：收盘价、涨跌幅各有1个缺失（中国平安），ROE、负债率各有1个缺失（招商银行）

数据整合的关键决策:

  1. 主表选择:以股票基本信息为主表,使用left join确保每只股票都保留
  2. 数据来源追踪:使用indicator=True标识每条数据是否成功匹配
  3. 列名冲突:使用suffixes参数处理重名列
  4. 缺失值处理:财务指标缺失可能意味着该股票尚未发布财报

53.7.2 性能优化策略

大数据集合并的性能陷阱:

列表 53.11: 大数据集合并的性能优化 
# =============================================================================
# 题目：大数据集合并的性能优化
# =============================================================================
# 本任务演示如何优化大规模数据集的合并性能
# 场景：500万行行情数据与5000行财务数据的合并

# ==================== 导入必要的库 ====================
import pandas as pd
import numpy as np
import time  # 用于计时的库

# ==================== 创建大规模测试数据 ====================
n_stocks = 5000  # 股票数量
n_dates = 1000  # 交易日期数量
np.random.seed(42)  # 固定随机种子，保证每次运行生成相同的数据

# 生成股票行情数据（500万行）
# 场景：5000只股票在1000个交易日的收盘价数据
quotes = pd.DataFrame({
    # 股票代码列：每只股票的代码重复1000次（对应1000个交易日）
    # np.repeat()：重复数组，[f'{i:06d}.SH' for i in range(n_stocks)]生成股票代码列表
    #              每个代码重复n_dates次
    '股票代码': np.repeat([f'{i:06d}.SH' for i in range(n_stocks)], n_dates),
    # 日期列：1000个日期重复5000次
    # list(pd.date_range(...)) * n_stocks：将日期列表复制5000次
    '日期': list(pd.date_range('2020-01-01', periods=n_dates)) * n_stocks,
    # 收盘价列：生成500万个10到100之间的随机数
    '收盘价': np.random.uniform(10, 100, n_stocks * n_dates)
})

# 生成财务数据（5000行）
# 场景：5000只股票的季度财务指标
financials = pd.DataFrame({
    '股票代码': [f'{i:06d}.SH' for i in range(n_stocks)],  # 5000只股票的代码
    'ROE': np.random.uniform(5, 30, n_stocks),  # ROE：5%到30%之间的随机数
    '市值': np.random.uniform(50, 5000, n_stocks)  # 市值：50亿到5000亿之间的随机数
})

# ==================== 方法1：未优化的合并 ====================
# 场景：直接合并，不进行任何优化处理
print('开始未优化的合并...')
start_time = time.time()  # 记录开始时间
# pd.merge()：基于股票代码合并500万行行情数据和5000行财务数据
# 默认情况下，Pandas会对合并键进行排序，这在数据量大时很耗时
result_slow = pd.merge(quotes, financials, on='股票代码')
slow_time = time.time() - start_time  # 计算耗时

# ==================== 方法2：优化后的合并（设置数据类型）====================
# 优化策略1：将字符串类型的键值转换为category类型
# category类型使用整数编码，比较和匹配的速度更快
quotes_opt = quotes.copy()
# .astype('category')：将股票代码列转换为category类型
#                   - 对于重复值多的列（如500万行中只有5000个唯一值），效率提升显著
quotes_opt['股票代码'] = quotes_opt['股票代码'].astype('category')

financials_opt = financials.copy()
financials_opt['股票代码'] = financials_opt['股票代码'].astype('category')

print('开始优化后的合并...')
start_time = time.time()
# pd.merge()：合并优化后的数据
result_fast = pd.merge(quotes_opt, financials_opt, on='股票代码')
fast_time = time.time() - start_time

# ==================== 性能对比 ====================
print(f'未优化合并时间: {slow_time:.2f}秒')
print(f'优化后合并时间: {fast_time:.2f}秒')
print(f'性能提升: {slow_time/fast_time:.1f}倍')
# 输出解读：优化后的合并通常能提升2-5倍的性能
# 提升幅度取决于数据规模和硬件配置

性能优化清单:

✅ 键值类型优化:将字符串键值转换为category类型 ✅ 索引优化:对键值列建立索引(df.set_index()) ✅ 避免重复:合并前检查并删除重复数据 ✅ 分块处理:超大文件考虑分块读取和合并 ✅ 使用Dask:超出内存容量时使用并行计算框架

53.8 高级主题复杂连接条件

53.8.1 多键连接

列表 53.12: 基于多个键值进行连接 
# =============================================================================
# 题目：基于多个键值进行连接
# =============================================================================
# 本任务演示如何基于多个列（股票代码+日期）进行合并
# 场景：需要精确匹配股票和日期，确保同一股票在同一天的行情和财务数据合并

# ==================== 创建包含日期的行情数据 ====================
# 场景：两只股票在两个交易日的收盘价数据
quotes = pd.DataFrame({
    '股票代码': ['600519.SH', '600519.SH', '000858.SZ'],
    '日期': ['2024-01-01', '2024-01-02', '2024-01-01'],
    '收盘价': [1850.0, 1870.0, 158.5]  # 注意：五粮液只有1月1日的数据
})

# ==================== 创建包含日期的财务数据 ====================
# 场景：两只股票在两个交易日的估值指标
financals = pd.DataFrame({
    '股票代码': ['600519.SH', '600519.SH', '000858.SZ'],
    '日期': ['2024-01-01', '2024-01-02', '2024-01-01'],
    'PE': [35.2, 35.8, 25.8]  # 市盈率数据
})

# ==================== 基于股票代码和日期两个键进行合并 ====================
# pd.merge()：多键连接
# 参数说明：
#   on=['股票代码', '日期']：指定多个键值列
#                          - 只有当两个键都匹配时，行才会被连接
#                          - 相当于 SQL 的 ON a.股票代码=b.股票代码 AND a.日期=b.日期
#   how='inner'：内连接，只保留两边都有的行
merged = pd.merge(
    quotes,
    financals,
    on=['股票代码', '日期'],  # 多键连接：同时匹配股票代码和日期
    how='inner'
)

print('多键连接结果:')
print(merged)
# 输出解读：3行数据都成功匹配
# - 贵州茅台1月1日：股票代码和日期都匹配
# - 贵州茅台1月2日：股票代码和日期都匹配
# - 五粮液1月1日：股票代码和日期都匹配
# 应用场景：确保分析的是同一股票在同一天的完整数据
#           避免错误地将不同日期的数据拼接在一起

多键连接的数学含义:

\[ R \bowtie_{k_1, k_2} S = \{ (r, s) \mid r[k_1] = s[k_1] \land r[k_2] = s[k_2] \} \]

只有所有指定的键值都匹配时,两行数据才会被连接。

53.9 本章小结

要点:

  • concat 沿指定轴堆叠:axis=0 纵向追加行、axis=1 横向拼接列;ignore_index=True 重排整数索引;列名或索引对不上的位置填 NaN
  • merge 按键值连接:on 指定连接键,how='inner'/'left'/'right'/'outer' 决定保留哪些行;多键连接用列表 on=['键1', '键2'],所有键都匹配才连接
  • join 以行索引为键横向合并,适合两张表行标签已对齐的场景,lsuffix/rsuffix 处理同名列
  • 拼接前先对齐结构:纵向拼要求列结构一致,横向拼要求行索引或键值能对齐;时间序列按日期索引拼接后常需再排序与去重
  • 连接类型的选择对应集合关系:inner 取交集、outer 取并集,left/right 以一侧为准补齐另一侧

易错点:

  • merge(how='inner') 会静默丢弃不匹配的行,连接后行数变少未必是错误,但必须先确认是否有意为之
  • concat 纵向拼接默认保留各自原索引,出现重复索引后用 loc 取行会一次取出多行;需要 ignore_index=True 或 reset_index()
  • 键列数据类型不一致(如字符串 '600519' 与整数 600519)会使连接全部失败,得到空表
  • 两表存在同名列又不加后缀参数时,合并会报错或覆盖数据
  • concat 的 axis 表示堆叠方向,与 merge 的 how(连接方式)语义完全不同,不能混用

53.10 动手与思考

以下练习每题附参考答案(默认折叠)。请先独立完成并写下你的判断,再点开对照,最后上机验证。

  1. 输出预测:不运行代码,先写出下面代码的输出结果,再上机检验你的判断。

    import pandas as pd

a = pd.DataFrame({'代码': ['600519', '000858'], '收盘价': [1850.0, 220.0]})
b = pd.DataFrame({'代码': ['600519', '600036'], '市盈率': [45, 8]})
inner = pd.merge(a, b, on='代码', how='inner')
outer = pd.merge(a, b, on='代码', how='outer')
print(inner.shape, outer.shape)
print(sorted(outer['代码'].tolist()))

    参考答案（先写下你的预测再点开）

    解题思路：连接键是”代码”。两表只有 600519 一个共同键，how='inner' 取交集只保留 1 行；how='outer' 取并集保留 3 行。列数上 merge 会保留连接键并拼接两表的非键列（收盘价、市盈率），所以分别是 (1, 3) 与 (3, 3)。外连接中无匹配的位置（600858 缺市盈率、600036 缺收盘价）填 NaN。排序后代码列表为 ['000858', '600036', '600519']。

    # 验证脚本：内连接与外连接的形状与键集合
import pandas as pd  # 导入pandas库
a = pd.DataFrame({'代码': ['600519', '000858'], '收盘价': [1850.0, 220.0]})  # 左表
b = pd.DataFrame({'代码': ['600519', '600036'], '市盈率': [45, 8]})  # 右表
inner = pd.merge(a, b, on='代码', how='inner')  # 内连接：键的交集
outer = pd.merge(a, b, on='代码', how='outer')  # 外连接：键的并集，缺失处补NaN
print(inner.shape, outer.shape)  # merge保留键列，故列数均为3
print(sorted(outer['代码'].tolist()))  # 外连接键升序排列

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

    (1, 3) (3, 3)
['000858', '600036', '600519']

    回扣主线：inner 取交集、outer 取并集的集合关系见第 章节 12 章与本章”要点”第 5 条。

  2. 自测回忆:不看正文,分别说出 concat、merge、join 的适用场景与最关键的一个参数;再写出内连接与外连接结果行数之间的关系式。

    参考答案（点开前请先独立完成）

    解题思路：适用场景——concat 用于”结构对得上的堆叠”：两表列结构一致时纵向追加行、行索引对齐时横向拼接列，最关键的参数是 axis（0 纵向、1 横向）；merge 用于”按键值的横向连接”：两表以某（些）列为键配对，最关键的参数是 how（inner/left/right/outer 决定保留哪些行）；join 用于”按行索引的横向合并”：适合两表行标签已对齐的场景，最关键的参数是 lsuffix/rsuffix（处理同名列）。行数关系式（键值唯一时）：内连接行数等于两表键交集的元素个数，不超过 min(左表行数, 右表行数)；外连接行数等于键并集的元素个数，即 左表行数 + 右表行数 − 内连接行数；若键有重复，匹配行按笛卡尔积展开，上述等号不再成立。

    回扣主线：三个工具的分工与参数见第 章节 12 章”要点”第 1—3 条。

  3. 变式任务(平台任务同型改造):平台任务2把同期(2019-01-02 至 07-31)两份不同股票的收盘价数据纵向拼接,现改为 pd.concat([price_JantoMar, price_AprtoJui], axis=1) 横向拼接,预测两者的 shape 与缺失值分布有何不同,再上平台验证。(注意:两份 Sheet 日期范围完全相同,横向拼接不会产生 NaN——这与“索引部分重叠才补 NaN”的一般结论是什么关系?)

    参考答案（点开前请先独立完成）

    解题思路：Sheet1 是中国移动、中国电信、中国人寿 3 列，Sheet2 是中国铝业、中国海洋石油 2 列，两表都是 2019-01-02 至 07-31 共 146 个交易日、行索引完全相同、列名互不重叠。纵向拼接（平台任务 2 原样）：行数相加、列取并集，得 (292, 5)，前 146 行的中国铝业两列与后 146 行的前三列全部补 NaN，合计 730 个（146×3 + 146×2）；横向拼接（本题变式）：行索引取交集对齐、列直接并排，得 (146, 5)，因为两表索引完全相同（交集=并集），没有任何错位位置，缺失值为 0 个。与一般结论的关系：横向拼接”对不上的位置补 NaN”的规则并没有失效——补 NaN 发生在索引部分重叠或完全错开时，本例两表索引完全重叠，属于”交集恰等于并集”的极端特例，不触发补齐；一般结论是该规则的完备表述，特例只是它的边界情形。以下在 OSS 直链数据上实跑验证（拉取日期 2026-08-27；若链接失效，可到教学平台运行平台任务同款代码验证，或用任意两份同长度日期序列的收盘价演练，代码不变）。

    # 变式脚本：纵向与横向拼接的形状及缺失对照
import pandas as pd  # 导入pandas库
url = 'https://huoran.oss-cn-shenzhen.aliyuncs.com/20220821/xlsx/1561220500528062464.xlsx'  # 两份Sheet所在工作簿
price_JantoMar = pd.read_excel(url, sheet_name='Sheet1', header=0, index_col=0)  # 读入3只股票收盘价
price_AprtoJui = pd.read_excel(url, sheet_name='Sheet2', header=0, index_col=0)  # 读入另2只股票收盘价
print(price_JantoMar.shape, price_AprtoJui.shape)  # 两表均为146行
print(price_JantoMar.index.equals(price_AprtoJui.index))  # 行索引是否完全相同
axis0 = pd.concat([price_JantoMar, price_AprtoJui])  # 纵向拼接（平台任务2原样）
axis1 = pd.concat([price_JantoMar, price_AprtoJui], axis=1)  # 横向拼接（本题变式）
print(axis0.shape, int(axis0.isna().sum().sum()))  # 纵向：(292,5)与缺失总数
print(axis1.shape, int(axis1.isna().sum().sum()))  # 横向：(146,5)与缺失总数

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

    (146, 3) (146, 2)
True
(292, 5) 730
(146, 5) 0

    注意：以上为变式代码；列表 53.2 的平台任务仍须按原始代码原样输入教学平台。

    回扣主线：concat 沿指定轴堆叠、对不上的位置填 NaN 见第 章节 12 章与本章”要点”第 1 条。

  4. 思考题:日频行情数据与季频财务数据若按”股票代码+日期”直接 merge,会丢掉大量行。为什么会这样?如果要”每个交易日都匹配最近一次已披露的财报”,你会如何设计对齐方案?

    参考答案（点开前请先独立完成）

    解题思路：丢行的原因是连接键几乎永不精确相等——行情表的”日期”是每个交易日，财报表的”日期”是披露日（每季度一个），一个披露日往往不是交易日、或者行情表中根本没有该日期的行；how='inner' 只保留两侧键完全相同的行，于是绝大多数交易日匹配失败被静默丢弃，剩下的可能只有零星几行甚至空表。对齐方案：这是典型的”按时间就近向前匹配”问题，应使用 pd.merge_asof——先把两表按日期排序，以行情表为左表、direction='backward' 让每个交易日匹配”不晚于该日”的最近一次披露，by='股票代码' 保证只在同一只股票内部就近匹配（需要时 allow_exact_matches=True 保留恰好同日的匹配）；等价的替代方案是为每份财报构造”披露日至下一披露日”的有效区间，再做区间连接。这样每个交易日都携带最近一次已披露的财报字段，且不产生行数膨胀。

    回扣主线：merge(how='inner') 静默丢弃不匹配行的告诫与键类型一致性问题见第 章节 12 章与本章”易错点”第 1、3 条。